﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using XQ.WeChatOfficialAccount.DAL;
using XQ.WeChatOfficialAccount.DAL.Model;

namespace XQ.WeChatOfficialAccount.WebAPI.Excel
{
    /// <summary>
    /// Excel导出帮助类
    /// </summary>
    public class ExcelHelperClass<T> where T:class,new()
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="list">要导出的列表</param>
        /// <param name="path">导出的Excel模版</param>
        /// <param name="index">Excel行索引</param>
        ///<param name="boot">根目录</param>
        /// <param name="name">文件名</param>
        /// <param name="rowheight">行高</param>
        /// <returns></returns>
        public static string ExportExcel(List<T> list,string boot, string path, int index,string name,int rowheight)
        {
            if (list.Count == 0)
            {
                return path;
            }
            path = boot + path;
            using (MemoryStream ms = new MemoryStream())
            {
                var entityProperties = list.FirstOrDefault().GetType().GetProperties();
                string extenison = Path.GetExtension(path);
                FileStream fs = File.OpenRead(path);
                IWorkbook workbook = null;
                if (extenison.Equals(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else {
                    workbook = new XSSFWorkbook(fs);
                }
                fs.Close();
                ISheet sheet = workbook.GetSheetAt(0);
                IRow rows = null;
                int i = 0;
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                foreach (var dbitem in list) {
                    rows = sheet.CreateRow(i++ + index);
                    for (int j = 0; j < entityProperties.Length; j++)
                    {
                        var prop = entityProperties[j];
                        var value = prop.GetValue(dbitem)?.ToString();
                        rows.CreateCell(j).SetCellValue(value ?? "");
                        //设置行高
                        rows.HeightInPoints = rowheight;
                        //设置边框 居中
                        ICell cell = rows.GetCell(j);
                        cell.CellStyle = style;
                    }
                }
                workbook.Write(ms);
                byte[] bt = new byte[] { };
                bt = ms.GetBuffer();
                string filename = name + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                string return_path= HttpContext.Current.Server.MapPath(string.Format("~/{0}", "Excel")) + "\\Output\\" +filename;
                FileStream fileStream = new FileStream(return_path, FileMode.OpenOrCreate);
                fileStream.Write(bt, 0, Convert.ToInt32(bt.Length));
                fileStream.Close();
                string return_path_ = "/Excel/Output/" + filename;
                return return_path_;
            }
        }
    }
}